USE [PeDALS_DLB]
GO


ALTER TABLE Item ADD
  ItemReceivedHashType varchar(50)
GO


ALTER TABLE Item DROP COLUMN [ItemAutoId]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PDL_ReadyToIngest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PDL_ReadyToIngest]
GO


ALTER PROCEDURE [dbo].[PDL_GetInitialValues] 
	@ProvenanceId INT,
	@SeriesId INT,
	@SeriesAcquisitionId INT
AS
BEGIN
	-- should constrain based on SeriesId to >SeriesAcquisitionId
	-- mapping in SeriesAcquisition table

	SELECT
		1 AS TAG,
		NULL AS PARENT,
		NEWID() AS [Values!1!SuperPackageId],
		ProvenanceName AS [Values!1!ProvenanceName],
		SeriesTitle AS [Values!1!SeriesTitle],
		SeriesIdentifier AS [Values!1!SeriesIdentifier],
		SeriesAcquisitionSource AS [Values!1!SeriesAcquisitionSource],
		SeriesAcquisitionDirectory AS [Values!1!SeriesAcquisitionDirectory],
		SeriesAcquisitionAccessionNumber AS [Values!1!SeriesAcquisitionAccessionNumber],
		SeriesAcquisitionIngestDate AS [Values!1!SeriesAcquisitionIngestDate],
		SeriesAcquisitionReceivedDate AS [Values!1!SeriesAcquisitionReceivedDate]
	FROM 
		Provenance,
		Series,
		SeriesAcquisition
	WHERE 
		Provenance.ProvenanceId = @ProvenanceId AND
		Series.SeriesId = @SeriesId AND
		SeriesAcquisition.SeriesAcquisitionId = @SeriesAcquisitionId
	FOR XML EXPLICIT
END
GO


CREATE PROCEDURE [dbo].[PDL_RETHROWERROR] AS
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO


CREATE Procedure [dbo].[PDL_InsertException]
	@ProvenanceId INT,
	@SeriesId INT,
	@SeriesAcquisitionId INT,
	@Desc INT
AS
	SET NOCOUNT ON

	INSERT INTO [PeDALS_DLB].[dbo].[Exceptions]
           ([ProvenanceId]
           ,[SeriesId]
           ,[SeriesAcquisitionId]
           ,[Description]
           ,[CreatedDate]
           ,[CreatedBy])
	VALUES
           (
			@ProvenanceId,
			@SeriesId,
			@SeriesAcquisitionId,
			@Desc,
			GETDATE(),
			'BizTalk'
           )
GO


CREATE PROCEDURE [dbo].[PDL_InsertItemDates]
  @ItemId INT,
  @Names VARCHAR(MAX) = NULL,
  @Values VARCHAR(MAX) = NULL,
  @CreatedDate VARCHAR(50) = '',
  @CreatedBy VARCHAR(50) = ''
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @EndLoop INT
	DECLARE @Delimiter CHAR
	DECLARE @LengthNamesToParse INT
	DECLARE @LengthValuesToParse INT
	DECLARE @LengthName INT
	DECLARE @LengthValue INT
	DECLARE @DelimName INT
	DECLARE @DelimValue INT
	DECLARE @NamesToParse VARCHAR(MAX)
	DECLARE @ValuesToParse VARCHAR(MAX)
	DECLARE @ParsedName VARCHAR(MAX)
	DECLARE @ParsedValue VARCHAR(MAX)
	DECLARE @Id INT

	BEGIN TRANSACTION

	BEGIN TRY
		SET @EndLoop = 0
		SET @Delimiter = ','
		SET @NamesToParse = @Names
		SET @ValuesToParse = @Values

		IF(@NamesToParse IS NOT NULL AND @ValuesToParse IS NOT NULL)
		BEGIN
			WHILE @EndLoop = 0
			BEGIN
				SET @LengthNamesToParse = LEN(@NamesToParse)
				SET @LengthValuesToParse = LEN(@ValuesToParse)
				SET @DelimName = CHARINDEX(@Delimiter, @NamesToParse) - 1
				SET @DelimValue = CHARINDEX(@Delimiter, @ValuesToParse) - 1

				IF(@DelimName < 0 OR @DelimValue < 0) -- delimiters not found
				BEGIN
					-- use entire string
					SET @LengthName = @LengthNamesToParse
					SET @LengthValue = @LengthValuesToParse
					SET @DelimName = @LengthName -- index past end of string
					SET @DelimValue = @LengthValue -- index past end of string
				END
				ELSE
				BEGIN
					SET @LengthName = @DelimName
					SET @LengthValue = @DelimValue
				END

				IF(@LengthName > 0 AND @LengthValue > 0) -- we have valid strings
				  BEGIN
					SET @ParsedName = RTRIM(LTRIM(LEFT(@NamesToParse, @LengthName)))
					SET @ParsedValue = RTRIM(LTRIM(LEFT(@ValuesToParse, @LengthValue)))

					IF(@ParsedName = 'Marriage Date')
						SET @Id = 5
					ELSE IF(@ParsedName = 'Recording Date')
						SET @Id = 6
					ELSE
						SET @Id = NULL

					IF(@Id IS NOT NULL)
					BEGIN
						INSERT INTO [PeDALS_DLB].[dbo].[ItemDate]
							(
							[ItemId],
							[QualifierId],
							[ItemDateValue],
							[CreatedDate],
							[CreatedBy]
							)
						VALUES
							(
							@ItemId,
							@Id,
							@ParsedValue,
							@CreatedDate,
							@CreatedBy
							)
					END

					IF(@LengthName = @LengthNamesToParse OR @LengthValue = @LengthValuesToParse)
						SET @EndLoop = 1 -- end of strings, nothing more to parse
					ELSE
					BEGIN
						SET @NamesToParse = RIGHT(@NamesToParse, @LengthNamesToParse - @LengthName - 1)
						SET @ValuesToParse = RIGHT(@ValuesToParse, @LengthValuesToParse - @LengthValue - 1)
					END
				  END
				ELSE
					SET @EndLoop = 1
			END
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION

		EXEC dbo.PDL_RETHROWERROR
	END CATCH
END
GO


CREATE PROCEDURE [dbo].[PDL_InsertItemKeywords]
  @ItemId INT,
  @Names VARCHAR(MAX) = NULL,
  @Values VARCHAR(MAX) = NULL,
  @CreatedDate VARCHAR(50) = '',
  @CreatedBy VARCHAR(50) = ''
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @EndLoop INT
	DECLARE @Delimiter CHAR
	DECLARE @LengthNamesToParse INT
	DECLARE @LengthValuesToParse INT
	DECLARE @LengthName INT
	DECLARE @LengthValue INT
	DECLARE @DelimName INT
	DECLARE @DelimValue INT
	DECLARE @NamesToParse VARCHAR(MAX)
	DECLARE @ValuesToParse VARCHAR(MAX)
	DECLARE @ParsedName VARCHAR(MAX)
	DECLARE @ParsedValue VARCHAR(MAX)

	BEGIN TRANSACTION

	BEGIN TRY
		SET @EndLoop = 0
		SET @Delimiter = ','
		SET @NamesToParse = @Names
		SET @ValuesToParse = @Values

		IF(@NamesToParse IS NOT NULL AND @ValuesToParse IS NOT NULL)
		BEGIN
			WHILE @EndLoop = 0
			BEGIN
				SET @LengthNamesToParse = LEN(@NamesToParse)
				SET @LengthValuesToParse = LEN(@ValuesToParse)
				SET @DelimName = CHARINDEX(@Delimiter, @NamesToParse) - 1
				SET @DelimValue = CHARINDEX(@Delimiter, @ValuesToParse) - 1

				IF(@DelimName < 0 OR @DelimValue < 0) -- delimiters not found
				BEGIN
					-- use entire string
					SET @LengthName = @LengthNamesToParse
					SET @LengthValue = @LengthValuesToParse
					SET @DelimName = @LengthName -- index past end of string
					SET @DelimValue = @LengthValue -- index past end of string
				END
				ELSE
				BEGIN
					SET @LengthName = @DelimName
					SET @LengthValue = @DelimValue
				END

				IF(@LengthName > 0 AND @LengthValue > 0) -- we have valid strings
				  BEGIN
					SET @ParsedName = RTRIM(LTRIM(LEFT(@NamesToParse, @LengthName)))
					SET @ParsedValue = RTRIM(LTRIM(LEFT(@ValuesToParse, @LengthValue)))

					INSERT INTO [PeDALS_DLB].[dbo].[ItemKeyword]
						(
						[ItemId],
						-- should there be a keyword name column?
						[Keyword],
						[CreatedDate],
						[CreatedBy]
						)
					VALUES
						(
						@ItemId,
						@ParsedValue,
						@CreatedDate,
						@CreatedBy
						)

					IF(@LengthName = @LengthNamesToParse OR @LengthValue = @LengthValuesToParse)
						SET @EndLoop = 1 -- end of strings, nothing more to parse
					ELSE
					BEGIN
						SET @NamesToParse = RIGHT(@NamesToParse, @LengthNamesToParse - @LengthName - 1)
						SET @ValuesToParse = RIGHT(@ValuesToParse, @LengthValuesToParse - @LengthValue - 1)
					END
				  END
				ELSE
					SET @EndLoop = 1
			END
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION

		EXEC dbo.PDL_RETHROWERROR
	END CATCH
END
GO


CREATE PROCEDURE [dbo].[PDL_InsertItemParties]
  @ItemId INT,
  @Names VARCHAR(MAX) = NULL,
  @Values VARCHAR(MAX) = NULL,
  @CreatedDate VARCHAR(50) = '',
  @CreatedBy VARCHAR(50) = ''
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @EndLoop INT
	DECLARE @Delimiter CHAR
	DECLARE @LengthNamesToParse INT
	DECLARE @LengthValuesToParse INT
	DECLARE @LengthName INT
	DECLARE @LengthValue INT
	DECLARE @DelimName INT
	DECLARE @DelimValue INT
	DECLARE @NamesToParse VARCHAR(MAX)
	DECLARE @ValuesToParse VARCHAR(MAX)
	DECLARE @ParsedName VARCHAR(MAX)
	DECLARE @ParsedValue VARCHAR(MAX)
	DECLARE @Id INT

	DECLARE @BrideFirstName VARCHAR(50)
	DECLARE @BrideMiddleName VARCHAR(50)
	DECLARE @BrideLastName VARCHAR(50)
	DECLARE @GroomFirstName VARCHAR(50)
	DECLARE @GroomMiddleName VARCHAR(50)
	DECLARE @GroomLastName VARCHAR(50)

	BEGIN TRANSACTION

	BEGIN TRY
		SET @EndLoop = 0
		SET @Delimiter = ','
		SET @NamesToParse = @Names
		SET @ValuesToParse = @Values

		SET @BrideFirstName = ''
		SET @BrideMiddleName = ''
		SET @BrideLastName = ''
		SET @GroomFirstName = ''
		SET @GroomMiddleName = ''
		SET @GroomLastName = ''

		IF(@NamesToParse IS NOT NULL AND @ValuesToParse IS NOT NULL)
		BEGIN
			WHILE @EndLoop = 0
			BEGIN
				SET @LengthNamesToParse = LEN(@NamesToParse)
				SET @LengthValuesToParse = LEN(@ValuesToParse)
				SET @DelimName = CHARINDEX(@Delimiter, @NamesToParse) - 1
				SET @DelimValue = CHARINDEX(@Delimiter, @ValuesToParse) - 1

				IF(@DelimName < 0 OR @DelimValue < 0) -- delimiters not found
				BEGIN
					-- use entire string
					SET @LengthName = @LengthNamesToParse
					SET @LengthValue = @LengthValuesToParse
					SET @DelimName = @LengthName -- index past end of string
					SET @DelimValue = @LengthValue -- index past end of string
				END
				ELSE
				BEGIN
					SET @LengthName = @DelimName
					SET @LengthValue = @DelimValue
				END

				IF(@LengthName > 0 AND @LengthValue > 0) -- we have valid strings
				  BEGIN
					SET @ParsedName = RTRIM(LTRIM(LEFT(@NamesToParse, @LengthName)))
					SET @ParsedValue = RTRIM(LTRIM(LEFT(@ValuesToParse, @LengthValue)))

					IF(@ParsedName = 'Bride First Name')
						SET @BrideFirstName = @ParsedValue
					ELSE IF(@ParsedName = 'Bride Middle Name')
						SET @BrideMiddleName = @ParsedValue
					ELSE IF(@ParsedName = 'Bride Last Name')
						SET @BrideLastName = @ParsedValue
					ELSE IF (@ParsedName = 'Bride First Name')
						SET @GroomFirstName = @ParsedValue
					ELSE IF(@ParsedName = 'Bride Middle Name')
						SET @GroomMiddleName = @ParsedValue
					ELSE IF(@ParsedName = 'Bride Last Name')
						SET @GroomLastName = @ParsedValue

					IF(@LengthName = @LengthNamesToParse OR @LengthValue = @LengthValuesToParse)
						SET @EndLoop = 1 -- end of strings, nothing more to parse
					ELSE
					BEGIN
						SET @NamesToParse = RIGHT(@NamesToParse, @LengthNamesToParse - @LengthName - 1)
						SET @ValuesToParse = RIGHT(@ValuesToParse, @LengthValuesToParse - @LengthValue - 1)
					END
				  END
				ELSE
					SET @EndLoop = 1
			END

			IF(LEN(@BrideLastName) > 0)
			BEGIN
				INSERT INTO [PeDALS_DLB].[dbo].[ItemParty]
					(
					[ItemId],
					[QualifierId],
					[ItemPartyValue],
					[CreatedDate],
					[CreatedBy]
					)
				VALUES
					(
					@ItemId,
					7,
					@BrideLastName + ', ' + @BrideFirstName + ' ' + @BrideMiddleName,
					@CreatedDate,
					@CreatedBy
					)
			END

			IF(LEN(@GroomLastName) > 0)
			BEGIN
				INSERT INTO [PeDALS_DLB].[dbo].[ItemParty]
					(
					[ItemId],
					[QualifierId],
					[ItemPartyValue],
					[CreatedDate],
					[CreatedBy]
					)
				VALUES
					(
					@ItemId,
					8,
					@GroomLastName + ', ' + @GroomFirstName + ' ' + @GroomMiddleName,
					@CreatedDate,
					@CreatedBy
					)
			END
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION

		EXEC dbo.PDL_RETHROWERROR
	END CATCH
END
GO



CREATE Procedure [dbo].[PDL_ReadyToIngestByIds]
	@SeriesAcquisitionId INT,
	@SeriesId INT
AS
	BEGIN
		SET NOCOUNT ON

		SELECT SeriesAcquisitionReadyToIngest
		FROM SeriesAcquisition
		WHERE SeriesId = @SeriesId
		AND	SeriesAcquisitionId = @SeriesAcquisitionId
		FOR XML AUTO
	END
GO


CREATE PROCEDURE [dbo].[PDL_RejectSeriesAcquisition]
  @SeriesId INT,
  @SeriesAcquisitionId INT
AS
BEGIN
	BEGIN TRANSACTION

	SET NOCOUNT ON

	BEGIN TRY
		CREATE TABLE #ItemIds (ItemId INT)

		INSERT INTO #ItemIds
		SELECT ItemId
		FROM [PeDALS_DLB].[dbo].[SeriesAcquisitionItem_LINK]
		WHERE SeriesId = @SeriesId
			AND SeriesAcquisitionId = @SeriesAcquisitionId

		DELETE FROM [PeDALS_DLB].[dbo].[SeriesAcquisitionItem_LINK]
		WHERE SeriesId = @SeriesId AND SeriesAcquisitionId = @SeriesAcquisitionId

		DELETE FROM [PeDALS_DLB].[dbo].[ItemGeoLocation_LINK]
		WHERE ItemId IN (SELECT ItemId FROM #ItemIds)

		DELETE FROM [PeDALS_DLB].[dbo].[ItemDate]
		WHERE ItemId IN (SELECT ItemId FROM #ItemIds)

		DELETE FROM [PeDALS_DLB].[dbo].[ItemKeyword]
		WHERE ItemId IN (SELECT ItemId FROM #ItemIds)

		DELETE FROM [PeDALS_DLB].[dbo].[ItemParty]
		WHERE ItemId IN (SELECT ItemId FROM #ItemIds)

		DELETE FROM [PeDALS_DLB].[dbo].[Item]
		WHERE ItemId IN (SELECT ItemId FROM #ItemIds)

		UPDATE [PeDALS_DLB].[dbo].[SeriesAcquisition]
		SET SeriesAcquisitionReadyToIngest = 3 -- reprocess
		WHERE SeriesId = @SeriesId
			AND SeriesAcquisitionId = @SeriesAcquisitionId

		DROP TABLE #ItemIds

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION

		EXEC dbo.PDL_RETHROWERROR
	END CATCH
END
GO


CREATE PROCEDURE [dbo].[PDL_InsertAIP]
  @SeriesId INT,
  @ItemTitle VARCHAR(250),
  @ItemDisplayTitle VARCHAR(250) = NULL,
  @ItemDescription VARCHAR(MAX) = NULL,
  @ItemExtentDescription VARCHAR(MAX) = NULL,
  @ItemOpenDate VARCHAR(50), -- convert to datetime
  @ItemSample VARCHAR(MAX) = NULL,
  @ItemReceivedFileName VARCHAR(250) = NULL,
  @ItemOriginatorIdentifier VARCHAR(50) = NULL,
  @ItemLanguage CHAR(3) = "ENG",
  @ItemAccessFacilitators VARCHAR(250) = NULL,
  @ItemExceptions VARCHAR(1250) = NULL,
  @HardwareId INT = NULL,
  @ItemOperatingSystemId INT = NULL,
  @SoftwareId INT = 1,
  @DigestAlgorithmId INT = NULL,
  @ItemReceivedHashOriginator VARCHAR(50) = '',
  @ItemReceivedHashDate VARCHAR(50) = '', -- convert to datetime
  @ItemReceivedHashType VARCHAR(50) = NULL,
  @ItemReceivedHashValue VARCHAR(256) = '',
  @ItemTechnicalInfrastructure VARCHAR(1250) = NULL,
  @ItemGeneralNote VARCHAR(MAX) = NULL,
  @DigitalFormatId INT = 1,
  @FilingUnitId INT = NULL,
  @SuperPackageId INT = NULL,
  @CreatedDate VARCHAR(50) = '',
  @CreatedBy VARCHAR(50) = '',
  @SeriesAcquisitionId INT,
  @ItemGeoLocationId INT,
  @ItemGeoLocationValue VARCHAR(250) = '',
  @ItemDateQualifiers VARCHAR(MAX) = NULL,
  @ItemDateValues VARCHAR(MAX) = NULL,
  @ItemKeywords VARCHAR(MAX) = NULL,
  @ItemKeywordValues VARCHAR(MAX) = NULL,
  @ItemParties VARCHAR(MAX) = NULL,
  @ItemPartyValues VARCHAR(MAX) = NULL
AS
BEGIN
	DECLARE @ItemId INT

	BEGIN TRANSACTION

	SET NOCOUNT ON

	BEGIN TRY
		INSERT INTO [PeDALS_DLB].[dbo].[Item]
			(
			[SeriesId],
			[ItemTitle],
			[ItemDisplayTitle],
			[ItemDescription],
			[ItemExtentDescription],
			[ItemOpenDate], -- convert to datetime
			[ItemSample],
			[ItemReceivedFileName],
			[ItemOriginatorIdentifier],
			[ItemLanguage],
			[ItemAccessFacilitators],
			[ItemExceptions],
			[HardwareId],
			[OperatingSystemId],
			[SoftwareId],
			[DigestAlgorithmId],
			[ItemReceivedHashOriginator],
			[ItemReceivedHashDate], -- convert to datetime
			[ItemReceivedHashType],
			[ItemReceivedHashValue],
			[ItemTechnicalInfrastructure],
			[ItemGeneralNote],
			[DigitalFormatId],
			[FilingUnitId],
			[SuperPackageId],
			[CreatedDate],
			[CreatedBy]
			)
		VALUES
			(
			@SeriesId,
			@ItemTitle,
			@ItemDisplayTitle,
			@ItemDescription,
			@ItemExtentDescription,
			CAST(@ItemOpenDate AS DATETIME),
			@ItemSample,
			@ItemReceivedFileName,
			@ItemOriginatorIdentifier,
			@ItemLanguage,
			@ItemAccessFacilitators,
			@ItemExceptions,
			@HardwareId,
			@ItemOperatingSystemId,
			@SoftwareId,
			@DigestAlgorithmId,
			@ItemReceivedHashOriginator,
			CAST(@ItemReceivedHashDate AS DATETIME),
			@ItemReceivedHashType,
			@ItemReceivedHashValue,
			@ItemTechnicalInfrastructure,
			@ItemGeneralNote,
			@DigitalFormatId,
			@FilingUnitId,
			@SuperPackageId,
			@CreatedDate,
			@CreatedBy
			)

		SET @ItemId = CAST(SCOPE_IDENTITY() AS INT)

		INSERT INTO [PeDALS_DLB].[dbo].[SeriesAcquisitionItem_LINK]
			(
			[SeriesId],
			[ItemId],
			[SeriesAcquisitionId],
			[CreatedDate],
			[CreatedBy]
			)
		VALUES
			(
			@SeriesId,
			@ItemId,
			@SeriesAcquisitionId,
			@CreatedDate,
			@CreatedBy
			)

		INSERT INTO [PeDALS_DLB].[dbo].[ItemGeoLocation_LINK]
			(
			[ItemId],
			[GeoLocationId],
			[CreatedDate],
			[CreatedBy]
			)
		VALUES
			(
			@ItemId,
			@ItemGeoLocationId,
			@CreatedDate,
			@CreatedBy
			)

		EXEC PDL_InsertItemDates @ItemId, @ItemDateQualifiers, @ItemDateValues, @CreatedDate, @CreatedBy
		EXEC PDL_InsertItemKeywords @ItemId, @ItemKeywords, @ItemKeywordValues, @CreatedDate, @CreatedBy
		EXEC PDL_InsertItemParties @ItemId, @ItemParties, @ItemPartyValues, @CreatedDate, @CreatedBy

		SELECT 1 AS TAG
				, NULL AS PARENT
				, @ItemId AS [AIP!1!ItemId]
				, @SeriesId AS [AIP!1!SeriesId]
				, @SeriesAcquisitionId AS [AIP!1!SeriesAcquisitionId]
		FOR XML EXPLICIT

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION

		EXEC dbo.PDL_RETHROWERROR
	END CATCH
END
GO